import pymysql
from pymysql.constants import CLIENT

# 数据库连接配置
config = {
    'host': 'localhost',  # MySQL服务器地址
    'port': 3306,         # MySQL服务器端口
    'user': 'root',       # 用户名
    'password': '14779895wmm', # 替换为你的MySQL密码
    'client_flag': CLIENT.MULTI_STATEMENTS  # 允许执行多条SQL语句
}

try:
    # 连接到MySQL服务器
    connection = pymysql.connect(**config)
    cursor = connection.cursor()

    # 创建数据库
    create_db_sql = """
    CREATE DATABASE IF NOT EXISTS xm 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;
    """

    # 创建表
    create_table_sql = """
    USE xm;
    CREATE TABLE IF NOT EXISTS jobs (
      jid INT(10) AUTO_INCREMENT,
      job_name VARCHAR(200) COMMENT '工作名称',
      company_name VARCHAR(200) COMMENT '公司名称',
      job_sal VARCHAR(200) COMMENT '工资',
      job_loc VARCHAR(200) COMMENT '工作地址',
      job_exp VARCHAR(400) COMMENT '工作经验要求',
      job_edu VARCHAR(200) COMMENT '学历要求',
      PRIMARY KEY(jid)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='职位信息表';
    """

    # 执行SQL
    cursor.execute(create_db_sql)
    cursor.execute(create_table_sql)
    connection.commit()

    print("数据库和表创建成功！")

except pymysql.Error as e:
    print(f"操作失败: {e}")
    connection.rollback()

finally:
    if connection:
        connection.close()